package com.mjgy.repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mjgy.entity.MUserEntity;
import com.mjgy.entity.UserLocationEntity;

/**
* @Description: TODO
* @author LGC
* @date 2018年9月11日
* @version V1.0
*/
public interface UserLocationRepository extends JpaRepository<UserLocationEntity, Long> {

	UserLocationEntity findOneByUser(MUserEntity user);
	
	/**
	 * 根据用户当前的经纬度，计算附近的人（5公里内）的距离
	 * 
	 * @param userId
	 * @param lon
	 * @param lat
	 * @return
	 */
	@Query(value="select user_id, \n" +
			     "       (6371.138 * acos(" +
			     "			        cos( radians(:lat) ) \n" + 
			     "				   *cos( radians(latitude) ) \n" + 
			     "			       *cos( radians(longitude) - radians(:lon) ) \n" + 
			     "				   +sin( radians(:lat) ) \n" + 
			     "				   *sin( radians(latitude) ) \n" + 
			     "		  )) as distance \n" +
			     "from e_user_location \n" +
			     "having distance < 5 and user_id != :userId \n " +
			     "order by distance asc \n" + 
			     "limit 0, 20", nativeQuery = true)
	List<Map<String, Object>> getNearbyUserDistance(@Param("userId") Long userId, @Param("lon") BigDecimal lon, @Param("lat") BigDecimal lat);
}
